<?php

namespace App\Admin\Controllers;

use App\Admin\Extensions\ExcelExpoter;
use App\Admin\Extensions\Squad_monthExpoter;
use App\Models\Class_section_group;
use App\Models\Class_type;
use App\Models\Squad;
use App\Models\Squad_month;
use App\Models\Student;
use App\Models\Teacher;
use App\Models\Student_lost;

use Encore\Admin\Auth\Database\Administrator;
use Encore\Admin\Form;
use Encore\Admin\Grid;
use Encore\Admin\Layout\Row;
use Encore\Admin\Facades\Admin;
use Encore\Admin\Layout\Content;
use App\Http\Controllers\Controller;
use Encore\Admin\Controllers\ModelForm;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use App\Models\Credits;
use Encore\Admin\Widgets\Table;
use App\Models\Campus;
use App\Models\Apply;
use App\Models\Term;
use Illuminate\Support\Facades\Input;
use Encore\Admin\Widgets\Box;

class Lost_reportController extends Controller
{
    /**
     * Index interface.
     *
     * @return Content
     */
    public function my_index()
    {
        return Admin::content(function (Content $content){
            Admin::script($this->script());
            $content->header('学员流失');
            $content->description('报表');
            
            $content->body($this->grid());
            $search = Input::get();
            if (!empty($search)) {
                if ($search['term_year']!=''&&$search['term_season']!=''&&$search['start_week']!=''&&$search['end_week']!='') {
                    $content->body($this->total_view($search));
                    $content->body(function(Row $row) use ($search){
                        $row->column(5,$this->squad_batch_view($search));
                        $row->column(7,$this->squad_view($search));
                    });
                    $content->body(function(Row $row) use ($search){
                        $row->column(5,$this->tutor_view($search));
                        $row->column(7,$this->study_jindu_view($search));
                    });
                }
            }
        });
    }

    protected function grid()
    {
        return Admin::grid(Student_lost::class, function (Grid $grid) {
            $grid->disableActions();
            $grid->disableCreateButton();
            $grid->disablePagination();
            $grid->disableExport();
            $grid->disableRowSelector();
            $grid->expandFilter();

            $grid->filter(function ($filter) {
                $term = Term::where('status','0')->first();
                $week = [];
                for ($i=1; $i < 25; $i++) { 
                    $week[$i] = "第".$i."周";
                }
                //dd($week);
                $filter->column(1/4, function ($filter) use ($term){
                    $filter->disableIdFilter();
                    $filter->equal('term_year', '学年')->year()->default($term->year);
                });
                $filter->column(1/4, function ($filter) use ($term){
                    $filter->equal('term_season', '学期')->select(['1'=>'春季学期','2'=>'秋季学期'])->default($term->season);
                });
                $filter->column(1/4, function ($filter) use($week){
                    $filter->where(function($query) use($week) {
                        $query->where('week','>=',"{$this->input}");
                    },'开始周','start_week')->select($week)->default("1");
                });
                $filter->column(1/4, function ($filter) use($week,$term) {
                    $current_week = ceil((time()-strtotime($term->start_date))/3600/24/7);
                    $filter->where(function($query) use($current_week,$week,$term) {
                        $query->where('week','<=',"{$this->input}");
                    },'结束周','end_week')->select($week)->default("$current_week");
                });
            });
        });
    }

    public function total_view(array $search)
    {
        $total_headers = ['', '一段班总人数', '一段班流失人数', '一段班流失率', '一段班流失同周占比','二段班总人数', '二段班流失人数', '二段班流失率', '二段班流失同周占比'];
        //找出有哪些周有流失,得到一个数组
        $weeks = Student_lost::where(['term_year'=>$search['term_year'],'term_season'=>$search['term_season']])->whereBetween('week',[$search['start_week'],$search['end_week']])->groupBy('week')->pluck('week')->toArray();
        //dd($weeks);
        //一段班总人数和二段班总人数
        $section1 = Class_section_group::where('name','一段班')->pluck('id')->toArray();
        $section2 = Class_section_group::where('name','二段班')->pluck('id')->toArray();
        $squad1 = Squad::where('section',$section1[0])->pluck('id')->toArray();
        //dd($squad1);
        $squad2 = Squad::where('section',$section2[0])->pluck('id')->toArray();
        $total_rows = [];
        for ($i=0; $i <count($weeks) ; $i++) {
            //一段班对应周总人数
            $total_num1 = Squad_month::where(['year'=>$search['term_year'],'season'=>$search['term_season'],'week'=>$weeks[$i]])->whereIn('squad_id',$squad1)->sum('stu_num');
            //二段班对应周总人数
            $total_num2 = Squad_month::where(['year'=>$search['term_year'],'season'=>$search['term_season'],'week'=>$weeks[$i]])->whereIn('squad_id',$squad2)->sum('stu_num');
            //一段班对应周流失人数
            $lost_num1 = Student_lost::where(['squad_section'=>$section1[0],'week'=>$weeks[$i]])->sum('lost_students');
            //二段班对应周流失人数
            $lost_num2 = Student_lost::where(['squad_section'=>$section2[0],'week'=>$weeks[$i]])->sum('lost_students');

            $lost_week = "第".$weeks[$i]."周";
            
            if ($total_num1!='0'&&$lost_num1!='0') {
                //一段班流失率
                $lost_rate1 = round(($lost_num1/$total_num1)*100,3)."%";
            }else{
                $lost_rate1 = "N/A";
            }
            if ($total_num2!='0'&&$lost_num2!='0') {
                //二段班流失率
                $lost_rate2 = round(($lost_num2/$total_num2)*100,3)."%";
            }else{
                $lost_rate2 = "N/A";
            }
            if ($lost_num1!='0') {
                $week_lost_rate1 = round(($lost_num1/($lost_num1+$lost_num2))*100,3)."%";
                $week_lost_rate2 = round(($lost_num2/($lost_num1+$lost_num2))*100,3)."%";
            }else{
                 $week_lost_rate1 = "N/A";
            }
            if ($lost_num2!='0') {
                $week_lost_rate2 = round(($lost_num2/($lost_num1+$lost_num2))*100,3)."%";
            }else{
                 $week_lost_rate2 = "N/A";
            }
            

            $total_rows[$i][0] = $lost_week;
            $total_rows[$i][1] = $total_num1;
            $total_rows[$i][2] = $lost_num1;
            $total_rows[$i][3] = $lost_rate1;
            $total_rows[$i][4] = $week_lost_rate1;
            $total_rows[$i][5] = $total_num2;
            $total_rows[$i][6] = $lost_num2;
            $total_rows[$i][7] = $lost_rate2;
            $total_rows[$i][8] = $week_lost_rate2;
            //排序
            ksort($total_rows[$i]);
        }
        $total_table = new Table($total_headers, $total_rows,['total_table']);
        $total_box = new Box('数据总览', $total_table);
        return $total_box->solid()->style('danger');
    }

    public function squad_batch_view(array $search)
    {
        $squad_batch_headers = ['开班段', '流失人数'];
        $squad_batch_rows = Student_lost::where(['term_year'=>$search['term_year'],'term_season'=>$search['term_season']])->whereBetween('week',[$search['start_week'],$search['end_week']])->groupBy('squad_batch')->orderBy('lost_nums','desc')->get(['squad_batch',DB::raw('sum(lost_students) as lost_nums')])->toArray();
        $squad_batch_table = new Table($squad_batch_headers, $squad_batch_rows,['squad_batch_table']);
        $squad_batch_box = new Box('开班段统计', $squad_batch_table);
        return $squad_batch_box->solid()->style('primary');
    }

    public function squad_view(array $search)
    {
        $squad_headers = ['班级', '流失人数','班主任'];
        $squad_rows = Student_lost::where(['term_year'=>$search['term_year'],'term_season'=>$search['term_season']])->whereBetween('week',[$search['start_week'],$search['end_week']])->groupBy('squad_name')->orderBy('lost_nums','desc')->get(['squad_name',DB::raw('sum(lost_students) as lost_nums'),'tutor_name'])->toArray();
        
        $squad_table = new Table($squad_headers, $squad_rows,['squad_table']);
        $squad_box = new Box('班级统计', $squad_table);
        return $squad_box->solid()->style('success');
    }

    public function tutor_view(array $search)
    {
        $tutor_headers = ['班主任', '流失人数'];
        $tutor_rows = Student_lost::where(['term_year'=>$search['term_year'],'term_season'=>$search['term_season']])->whereBetween('week',[$search['start_week'],$search['end_week']])->groupBy('class_teacher')->orderBy('lost_nums','desc')->get(['tutor_name',DB::raw('sum(lost_students) as lost_nums')])->toArray();
        
        $tutor_table = new Table($tutor_headers, $tutor_rows,['tutor_table']);
        $tutor_box = new Box('班主任统计', $tutor_table);
        return $tutor_box->solid()->style('warning');
    }

    public function study_jindu_view(array $search)
    {
        $study_jindu_headers = ['章节', '流失人数'];
        $study_jindu_rows = Student_lost::where(['term_year'=>$search['term_year'],'term_season'=>$search['term_season']])->whereBetween('week',[$search['start_week'],$search['end_week']])->groupBy('study_jindu')->orderBy('lost_nums','desc')->get(['study_jindu',DB::raw('sum(lost_students) as lost_nums')])->toArray();
        
        $study_jindu_table = new Table($study_jindu_headers, $study_jindu_rows,['study_jindu_table']);
        $study_jindu_box = new Box('章节统计', $study_jindu_table);
        return $study_jindu_box->solid()->style('info');
    }

    public function script()
    {
        return <<<EOT
$(document).ready(function () {
    var printCounter = 0;
    $.extend( $.fn.dataTable.defaults, {
        language: {
            url:"/datatable_zh.txt"
        },
        scrollY: 370,
        "autoWidth": true,
        "pagingType": "simple_numbers",
        "order": [[ 1, 'desc' ]],
    });
    $('#total_table').DataTable({
        "info": false,
        "lengthChange": false,
        "pageLength": 5,
        "paging": false,
        scrollY: 150,
        "searching": false
        });
    $('#squad_batch_table').DataTable();
    $('#squad_table').DataTable();
    $('#tutor_table').DataTable();
    $('#study_jindu_table').DataTable({
        "columnDefs": [{ 
            "width": "85%", 
            "targets": 0 
        }]
        });

});
EOT;
    }
}
